CREATE function fn_load_all_packing_slip_emails ( @customer_id int, @customer_shipping_id int, @company_users bit, @external_users bit, @notifications bit )
RETURNS @fn_results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(300), Source varchar(50), DefaultChecked bit )

AS
BEGIN
	declare @salesOrder_Id int
	declare @customer_name varchar(65)
	declare @salesOrder_contact_person varchar(50)
	declare @so_def_bill_contact_person varchar(50)
	declare @so_def_ship_contact_person varchar(50)
	---declare @customer_id int
	--declare @odm_id int
	declare @email_count int
	declare @shipping_address_id int
	declare @billing_address_id int
	declare @contact_person varchar(50)
	declare @email varchar(300)
	declare @packing_slip_mail bit

	declare @emailCursor CURSOR

 	declare @results Table ( Customer_Id int, Contact_Person varchar(50), Email varchar(300), Source varchar(50), DefaultChecked bit )

	SELECT @customer_name = C_Name FROM Customer WHERE Customer_ID = @customer_id
	
	--DECLARE @PRINT_MSG VARCHAR (200)
	--SET @PRINT_MSG = ' @customer_id = ' + CONVERT(VARCHAR,@customer_id)+ ' @customer_name = ' + CONVERT(VARCHAR,@customer_name) + ' @shipping_address_id = ' + CONVERT(VARCHAR,@shipping_address_id) + ' @billing_address_id = ' + CONVERT(VARCHAR,@billing_address_id)
		
	---LOAD CUSTOMER CONTACT PERSON EMAIL
	SET @email_count = 0
	--SET @emailCursor = CURSOR FOR SELECT Contact_Person, Email FROM Customer_Contact_Persons_Table WHERE  ( Email IS NOT NULL ) AND ( Email <> '' ) AND ( Deactivated <> 1 ) AND ( Packing_Slip_Mail = 0 ) AND ( Customer_Id = @customer_id )
	SET @emailCursor = CURSOR FOR SELECT Contact_Person, Email, Packing_Slip_Mail FROM Customer_Contact_Persons_Table WHERE  ( Email IS NOT NULL ) AND ( Email <> '' ) AND ( Deactivated <> 1 ) AND ( Customer_Id = @customer_id )
	OPEN @emailCursor FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
 	WHILE @@fetch_status = 0
 	BEGIN
 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'CUSTOMER_CONTACT', @packing_slip_mail )

		SET @email_count = @email_count + 1
		set @contact_person = null
		set @email = null	
		set @packing_slip_mail = null	
 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor
	
	set @contact_person = null
	set @email = null		
 	
	---LOAD CUSTOMER EMAIL
	/*IF( @salesOrder_contact_person = @customer_name )
	BEGIN
		SELECT @contact_person = C.C_Contact_Person, @email = A.Email FROM Customer C INNER JOIN Address A ON C.C_Default_Billing_Address_ID = A.Address_ID WHERE ( C.Customer_ID = @customer_id )
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, @customer_name, @email, 'CUSTOMER' )--here contact name = customer_name 
		END
	
		set @contact_person = null
		set @email = null		
	END*/

	---LOAD BILLING CONTACT PERSON EMAIL
	SET @email_count = 0
	--SET @emailCursor = CURSOR FOR SELECT  Contact_Person, Email FROM Address_Contact_Persons_Table WHERE (Email IS NOT NULL) AND (Email <> '') AND ( Deactivated <> 1 ) AND ( Ship_Alerts = 0 ) AND (Address_Id = @billing_address_id )
	SET @emailCursor = CURSOR FOR SELECT ACPT.Contact_Person, ACPT.Email, ACPT.Packing_Slip_Mail FROM Customer C INNER JOIN Address_Contact_Persons_Table ACPT ON C.C_Default_Billing_Address_ID = ACPT.Address_Id WHERE (Email IS NOT NULL) AND (Email <> '') AND ( Deactivated <> 1 )  AND (C.Customer_ID = @customer_id)

	OPEN @emailCursor	
  	FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail

 	WHILE @@fetch_status = 0
 	BEGIN
		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'CUSTOMER_BILLING_CONTACT', @packing_slip_mail )

		SET @email_count = @email_count + 1
		set @contact_person = null
		set @email = null	
		set @packing_slip_mail = null	
 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor

	set @contact_person = null
	set @email = null	

	---LOAD SHIPPING CONTACT PERSON EMAIL
	SET @email_count = 0
	SET @emailCursor = CURSOR FOR 
	SELECT ACPT.Contact_Person, ACPT.Email, ACPT.Packing_Slip_Mail FROM Customer C INNER JOIN Address_Contact_Persons_Table ACPT ON C.C_Default_Shipping_Address_ID = ACPT.Address_Id WHERE  (Email IS NOT NULL) AND (Email <> '') AND ( Deactivated <> 1 )  AND (C.Customer_ID = @customer_id) 
	UNION SELECT ACPT.Contact_Person, ACPT.Email, ACPT.Packing_Slip_Mail FROM Customer_Shipping_Address_Table CSAT INNER JOIN Address_Contact_Persons_Table ACPT ON CSAT.Address_ID = ACPT.Address_Id WHERE  (Email IS NOT NULL) AND (Email <> '') AND ( Deactivated <> 1 ) AND  (CSAT.Customer_ID = @customer_id)

	OPEN @emailCursor	
  	FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail

 	WHILE @@fetch_status = 0
 	BEGIN
 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'CUSTOMER_SHIPPING_CONTACT', @packing_slip_mail )
		
		SET @email_count = @email_count + 1
		set @contact_person = null
		set @email = null	
		set @packing_slip_mail = null	
 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
 	END
 	CLOSE @emailCursor
	DEALLOCATE @emailCursor

	IF( @company_users = 0 )
	BEGIN
		SET @emailCursor = CURSOR FOR SELECT First_Name, Email, Packing_Slip_Mail FROM User_Table WHERE (Email IS NOT NULL) AND (Email <> '') AND (Disabled = 1)
		OPEN @emailCursor	
	  	FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
	
	 	WHILE @@fetch_status = 0
	 	BEGIN
	 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'USER', @packing_slip_mail )
	
			set @contact_person = null
			set @email = null	
			set @packing_slip_mail = null	
	 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
	 	END
	 	CLOSE @emailCursor
		DEALLOCATE @emailCursor

		set @contact_person = null
		set @email = null	

		/*
		SELECT @email = Packing_Slip_Emails FROM Notification_Emails_Table
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, @customer_name, @email, 'NOTIFICATION', 0 )--here contact name = customer_name 
		END*/		
	END
	IF( @external_users = 0 )
	BEGIN
		SET @emailCursor = CURSOR FOR SELECT  EUT.First_Name , EUT.Email, 0 FROM Location L INNER JOIN Customer_Shipping_Table CST ON L.Address_ID = CST.CST_Location_Address_Id INNER JOIN ExternalUser_Table EUT ON L.Vendor_ID = EUT.Vendor_ID WHERE  (EUT.Email IS NOT NULL) AND (EUT.Email <> '') AND (L.L_Type = 'Finished Goods') AND CST.CustomerShipping_ID = @customer_shipping_id
		OPEN @emailCursor	
	  	FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
	
	 	WHILE @@fetch_status = 0
	 	BEGIN
	 		INSERT INTO @results VALUES (@customer_id, @contact_person, @email, 'VENDOR_USER', @packing_slip_mail )
	
			set @contact_person = null
			set @email = null	
			set @packing_slip_mail = null	
	 		FETCH NEXT FROM @emailCursor INTO @contact_person, @email, @packing_slip_mail
	 	END
	 	CLOSE @emailCursor
		DEALLOCATE @emailCursor
	END
	IF( @notifications = 0 )
	BEGIN
		SELECT @email = Packing_Slip_Emails FROM Notification_Emails_Table
		IF ( @email IS NOT NULL AND RTRIM( LTRIM( @email ) ) <> '' )
		BEGIN
			INSERT INTO @results VALUES (@customer_id, '', @email, 'NOTIFICATION', 0 )--here contact name = customer_name 
		END
	END
	
	set @contact_person = null
	set @email = null		
 	
	insert @fn_results select * from @results
 	return
END
 